﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Synchronization.Data.Server;
using System.Data;

namespace Syncronization.Service
{
    public abstract class BaseSyncAdapter : SyncAdapter
    {
        private List<SyncAdapterProperty> uniqueKeys;

        /// <summary>
        /// Initializes a new instance of the <see cref="BaseSyncAdapter"/> class.
        /// </summary>
        public BaseSyncAdapter()
        {
            Properties = new List<SyncAdapterProperty>();

            TableName = EntityName();
            RegisterProperties();
            CreateCommands();
        }

        private void CreateCommands()
        {
            uniqueKeys = Properties.FindAll(item => item.IsUniqueKey);

            CreateInsertCommand();
            CreateDeleteCommand();
            CreateUpdateCommand();
            CreateDeleteRowsCommand();
            CreateUpdatedRowsCommand();
            CreateIncrementalInsertCommand();
            CreateDeletesCommand();
            CreateSelectIncrementalUpdatesCommand();
        }

        private string CreateDelimitedList(List<SyncAdapterProperty> properties)
        {
            int count = properties.Count;

            StringBuilder sb = new StringBuilder();

            for (int i = 0; i < count; i++)
            {
                sb.AppendFormat(
                    i < count ? "[{0}]," : "[{0}]", properties[i].Name);
            }

            return sb.ToString();
        }

        /// <summary>
        /// Creates the select incremental updates command.
        /// </summary>
        protected virtual void CreateSelectIncrementalUpdatesCommand()
        {
            string baseCommandText = @"SELECT {0} FROM dbo.[{1}] WHERE ([{2}] > @sync_last_received_anchor AND [{2}] <= @sync_new_received_anchor AND [{2}] <= @sync_last_received_anchor)";

            string selectProperties = CreateDelimitedList(Properties);

            this.SelectIncrementalUpdatesCommand = new System.Data.SqlClient.SqlCommand();
            this.SelectIncrementalUpdatesCommand.CommandText =
                string.Format(baseCommandText, selectProperties, TableName, ModifiedFlag(), CreationFlag());
            this.SelectIncrementalUpdatesCommand.CommandType = System.Data.CommandType.Text;
            this.SelectIncrementalUpdatesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
            this.SelectIncrementalUpdatesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.DateTime));
        }

        /// <summary>
        /// Creates the deletes command.
        /// </summary>
        protected virtual void CreateDeletesCommand()
        {
            string baseCommandText = "SELECT {0}, [{1}] FROM dbo.{2} WHERE (@sync_initialized " +
                    "= 1 AND [{1}] > @sync_last_received_anchor AND [{1}] <= @sync_" +
                    "new_received_anchor)";
            
            string uniqueKeyColumns = CreateDelimitedList(uniqueKeys);

            this.SelectIncrementalDeletesCommand = new System.Data.SqlClient.SqlCommand();
            this.SelectIncrementalDeletesCommand.CommandText =
                string.Format(baseCommandText, uniqueKeyColumns, DeleteFlag(), TableName);
            this.SelectIncrementalDeletesCommand.CommandType = System.Data.CommandType.Text;
            this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_initialized", System.Data.SqlDbType.Bit));
            this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
            this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.DateTime));
        }

        /// <summary>
        /// Creates the incremental insert command.
        /// </summary>
        protected virtual void CreateIncrementalInsertCommand()
        {
            string commandText = "SELECT {0}" +
                    "  FROM dbo.[{1}] WHERE ([{2}] > @sync_last_received_anch" +
                    "or AND [{2}] <= @sync_new_received_anchor)";

            string properties = CreateDelimitedList(Properties);

            this.SelectIncrementalInsertsCommand = new System.Data.SqlClient.SqlCommand();
            this.SelectIncrementalInsertsCommand.CommandText = string.Format(commandText,
                properties, TableName, CreationFlag());
            this.SelectIncrementalInsertsCommand.CommandType = System.Data.CommandType.Text;
            this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
            this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.DateTime));
        }

        /// <summary>
        /// Creates the updated rows command.
        /// </summary>
        protected virtual void CreateUpdatedRowsCommand()
        {
            //TODO:Implement
            //this.SelectConflictUpdatedRowsCommand = new System.Data.SqlClient.SqlCommand();
            //this.SelectConflictUpdatedRowsCommand.CommandText = "SELECT [UserID], [UserName], [Password], [FirstName], [LastName], [CreationDate]," +
            //        " [ModifiedDate] FROM dbo.[User] WHERE ([UserID] = @UserID)";
            //this.SelectConflictUpdatedRowsCommand.CommandType = System.Data.CommandType.Text;
            //this.SelectConflictUpdatedRowsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", System.Data.SqlDbType.Int));
        }

        /// <summary>
        /// Creates the delete rows command.
        /// </summary>
        protected virtual void CreateDeleteRowsCommand()
        {
            //TODO:Implement
            //this.SelectConflictDeletedRowsCommand = new System.Data.SqlClient.SqlCommand();
            //this.SelectConflictDeletedRowsCommand.CommandText = "SELECT [UserID], [DeletionDate] FROM dbo.User_Tombstone WHERE ([UserID] = @UserID" +
            //        ")";
            //this.SelectConflictDeletedRowsCommand.CommandType = System.Data.CommandType.Text;
            //this.SelectConflictDeletedRowsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", System.Data.SqlDbType.Int));
        }

        /// <summary>
        /// Creates the update command.
        /// </summary>
        protected virtual void CreateUpdateCommand()
        {
            //TODO:Implement
            //this.UpdateCommand = new System.Data.SqlClient.SqlCommand();
            //this.UpdateCommand.CommandText = @"UPDATE dbo.[User] SET [UserName] = @UserName, [Password] = @Password, [FirstName] = @FirstName, [LastName] = @LastName, [CreationDate] = @CreationDate, [ModifiedDate] = @ModifiedDate WHERE ([UserID] = @UserID) AND (@sync_force_write = 1 OR ([ModifiedDate] <= @sync_last_received_anchor)) SET @sync_row_count = @@rowcount";
            //this.UpdateCommand.CommandType = System.Data.CommandType.Text;
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserName", System.Data.SqlDbType.NVarChar));
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", System.Data.SqlDbType.NVarChar));
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar));
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", System.Data.SqlDbType.NVarChar));
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CreationDate", System.Data.SqlDbType.DateTime));
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModifiedDate", System.Data.SqlDbType.DateTime));
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", System.Data.SqlDbType.Int));
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_force_write", System.Data.SqlDbType.Bit));
            //this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
            //System.Data.SqlClient.SqlParameter updatecommand_sync_row_countParameter = new System.Data.SqlClient.SqlParameter("@sync_row_count", System.Data.SqlDbType.Int);
            //updatecommand_sync_row_countParameter.Direction = System.Data.ParameterDirection.Output;
            //this.UpdateCommand.Parameters.Add(updatecommand_sync_row_countParameter);
        }

        /// <summary>
        /// Creates the insert command.
        /// </summary>
        protected virtual void CreateInsertCommand()
        {
            //TODO:Implement
            //this.InsertCommand = new System.Data.SqlClient.SqlCommand();
            //this.InsertCommand.CommandText = @" SET IDENTITY_INSERT dbo.[User] ON INSERT INTO dbo.[User] ([UserID], [UserName], [Password], [FirstName], [LastName], [CreationDate], [ModifiedDate]) VALUES (@UserID, @UserName, @Password, @FirstName, @LastName, @CreationDate, @ModifiedDate) SET @sync_row_count = @@rowcount SET IDENTITY_INSERT dbo.[User] OFF ";
            //this.InsertCommand.CommandType = System.Data.CommandType.Text;
            //this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", System.Data.SqlDbType.Int));
            //this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserName", System.Data.SqlDbType.NVarChar));
            //this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", System.Data.SqlDbType.NVarChar));
            //this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar));
            //this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", System.Data.SqlDbType.NVarChar));
            //this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CreationDate", System.Data.SqlDbType.DateTime));
            //this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModifiedDate", System.Data.SqlDbType.DateTime));
            //System.Data.SqlClient.SqlParameter insertcommand_sync_row_countParameter = new System.Data.SqlClient.SqlParameter("@sync_row_count", System.Data.SqlDbType.Int);
            //insertcommand_sync_row_countParameter.Direction = System.Data.ParameterDirection.Output;
            //this.InsertCommand.Parameters.Add(insertcommand_sync_row_countParameter);
        }

        /// <summary>
        /// Creates the delete command.
        /// </summary>
        protected virtual void CreateDeleteCommand()
        {
            //TODO:Implement
            //this.DeleteCommand = new System.Data.SqlClient.SqlCommand();
            //this.DeleteCommand.CommandText = "DELETE FROM dbo.[User] WHERE ([UserID] = @UserID) AND (@sync_force_write = 1 OR (" +
            //        "[ModifiedDate] <= @sync_last_received_anchor)) SET @sync_row_count = @@rowcount";
            //this.DeleteCommand.CommandType = System.Data.CommandType.Text;
            //this.DeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", System.Data.SqlDbType.Int));
            //this.DeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_force_write", System.Data.SqlDbType.Bit));
            //this.DeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
            //System.Data.SqlClient.SqlParameter deletecommand_sync_row_countParameter = new System.Data.SqlClient.SqlParameter("@sync_row_count", System.Data.SqlDbType.Int);
            //deletecommand_sync_row_countParameter.Direction = System.Data.ParameterDirection.Output;
            //this.DeleteCommand.Parameters.Add(deletecommand_sync_row_countParameter);
        }

        /// <summary>
        /// Gets or sets the properties.
        /// </summary>
        /// <value>The properties.</value>
        public List<SyncAdapterProperty> Properties { get; set; }

        /// <summary>
        /// Registers the properties.
        /// </summary>
        public abstract void RegisterProperties();

        /// <summary>
        /// Entities the name.
        /// </summary>
        /// <returns></returns>
        public abstract string EntityName();

        /// <summary>
        /// Tombstones the table.
        /// </summary>
        /// <returns></returns>
        public abstract string TombstoneTable();

        /// <summary>
        /// Deletes the flag.
        /// </summary>
        /// <returns></returns>
        public abstract string DeleteFlag();

        /// <summary>
        /// Modifieds the flag.
        /// </summary>
        /// <returns></returns>
        public abstract string ModifiedFlag();

        /// <summary>
        /// Creations the flag.
        /// </summary>
        /// <returns></returns>
        public abstract string CreationFlag();
    }

    public class SyncAdapterProperty
    {
        /// <summary>
        /// Gets or sets the name.
        /// </summary>
        /// <value>The name.</value>
        public string Name { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether this instance is unique key.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if this instance is unique key; otherwise, <c>false</c>.
        /// </value>
        public bool IsUniqueKey { get; set; }

        /// <summary>
        /// Gets or sets the type of the DB.
        /// </summary>
        /// <value>The type of the DB.</value>
        public SqlDbType DBType { get; set; }
    }

}
